﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_WaitingListTable
    /// </summary>
    public class CompanyReport_WaitingListTable : CacheAbstraction
    {
        public DataTable GetWaitingListRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetWaitingListRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetWaitingListRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_WAITING_LIST_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxSelect =
                    "SUM(DECODE(jenis_order, 'PASANG BARU', 1, 0)) as \"PASANG BARU\", " +
                    "SUM(DECODE(jenis_order, 'MUTASI', 1, 0)) as \"MUTASI\", "+
                    "SUM(DECODE(jenis_order, 'CABUT', 1, 0)) as \"CABUT\", "+
                    "SUM(DECODE(jenis_order, 'ISOLIR', 1, 0)) as \"ISOLIR\", "+
                    "SUM(DECODE(jenis_order, 'BATAL PASANG BARU', 1, 0)) as \"BATAL PASANG BARU\", "+
                    "SUM(DECODE(jenis_order, 'BATAL MUTASI', 1, 0)) as \"BATAL MUTASI\", "+
                    "SUM(DECODE(jenis_order, 'BUKA ISOLIR', 1, 0)) as \"BUKA ISOLIR\", "+
                    "VALUE, "+
                    "MONTH, "+
                    "YEAR";
                String sxQuery = String.Format(
                    @"
                    select
                        {0}
                    from 
                        (
                            select
                                TO_CHAR(a.NETWORK_REQUEST_TYPE_DATE, 'MON/YY') VALUE,
                                CAST(TO_CHAR(a.NETWORK_REQUEST_TYPE_DATE, 'MM') as number) MONTH,
                                CAST(TO_CHAR(a.NETWORK_REQUEST_TYPE_DATE, 'YYYY') as number) YEAR,
                                a.NETWORK_REQUEST_TYPE_CODE jenis_order 
                            from 
                                t_olap_master_wl a
                                left join sni.t_network_order b
                                    on a.T_NETWORK_ORDER_ID = b.T_NETWORK_ORDER_ID
                                left join sni.t_network_order_pic c
                                    on b.T_NETWORK_ORDER_ID=c.T_NETWORK_ORDER_ID
                                left join sni.site_pic d
                                    on c.SITE_PIC_ID = d.SITE_PIC_ID
                                left join sni.site e
                                    on d.SITE_ID = e.SITE_ID
                            where 
                                c.P_CONTACT_PERSON_TYPE_ID = 9
                                and NVL(d.VALID_TO,sysdate) >= sysdate
                                and e.COMPANY_ID = :prmID
                                and TRUNC(a.NETWORK_REQUEST_TYPE_DATE) >= TRUNC(:prmStart)
                                and TRUNC(a.NETWORK_REQUEST_TYPE_DATE) < TRUNC(:prmEnd + 1)
                        )
                    group by
                        VALUE,
                        MONTH,
                        YEAR
                    order by
                        YEAR,
                        MONTH 
                    ",sxSelect);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetWaitingListDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetWaitingListDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetWaitingListDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_WAITING_LIST_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxQuery =
                    @"
                    select
                        a.NO_JARINGAN NETWORK_NO, 
                        a.NETWORK_REQUEST_TYPE_CODE jenis_order, 
                        a.NETWORK_REQUEST_TYPE_DATE tgl_order,
                        a.DM_ORDER_PROBLEM_NO MASALAH, 
                        a.STOP_CLOCK, 
                        a.DURASI_ORDER_NETTO LAMA_WAITING_LIST
                    from 
                        t_olap_master_wl a
                        left join sni.t_network_order b
                            on a.T_NETWORK_ORDER_ID = b.T_NETWORK_ORDER_ID
                        left join sni.t_network_order_pic c
                            on b.T_NETWORK_ORDER_ID=c.T_NETWORK_ORDER_ID
                        left join sni.site_pic d
                            on c.SITE_PIC_ID = d.SITE_PIC_ID
                        left join sni.site e
                            on d.SITE_ID = e.SITE_ID
                    where 
                        c.P_CONTACT_PERSON_TYPE_ID = 9
                        and NVL(d.VALID_TO,sysdate) >= sysdate
                        and e.COMPANY_ID = :prmID
                        and TRUNC(a.NETWORK_REQUEST_TYPE_DATE) >= TRUNC(:prmStart)
                        and TRUNC(a.NETWORK_REQUEST_TYPE_DATE) < TRUNC(:prmEnd + 1)
                    order by 
                        a.NO_JARINGAN desc
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}